package com.sihai.ehr.service.impl;

import com.sihai.core.*;
import com.sihai.ehr.model.biz.EmployeeinfoBase;
import com.sihai.ehr.model.biz.LeaveOfficeApply;
import com.sihai.ehr.model.biz.LeaveOfficeApplyAuditing;
import com.sihai.ehr.model.biz.LeaveOfficeApplyHrAuditInfo;
import com.sihai.ehr.model.sys.AuditStatus;
import com.sihai.ehr.model.sys.Organization;
import com.sihai.ehr.model.sys.User;
import com.sihai.ehr.repository.AuditingRepository;
import com.sihai.ehr.service.LeaveOfficeApplyService;
import com.sihai.ehr.web.pojo.SearchFormPojo;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.annotation.Resource;
import java.text.DecimalFormat;
import java.util.*;

/**
 * author: wxn
 * @since 13-4-25 19:59
 * 离职申请Service
 */
@Service("leaveOfficeApplyService")
public class LeaveOfficeApplyServiceImpl implements LeaveOfficeApplyService {
    @Resource
    private DataAccessor dataAccessor;
    @Resource
    private AuditingRepository auditingRepository;

    @Override
    public void save(LeaveOfficeApply apply) throws Exception {
        Assert.notNull(apply, "LeaveOfficeApply is required");
        Date date = apply.getLeaveDate();
        Assert.notNull(date, "LeaveOfficeApply date is required");
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        apply.setYear(c.get(Calendar.YEAR));
        apply.setMonth(c.get(Calendar.MONTH) + 1);
        apply.setDay(date.getDay());
        boolean isCreate = (apply.getId() == null || apply.getId() <= 0);
        dataAccessor.save(apply);
        if(isCreate){
            saveLeaveOfficeApplyAuditing(apply, 1);
        }
    }

    @Override
    public LeaveOfficeApply get(Long id) throws Exception {
        Assert.notNull(id, "LeaveOfficeApply id is required");
        return dataAccessor.get(LeaveOfficeApply.class, id);
    }

    @Override
    public boolean delete(String ids) throws Exception {
        final String deleteSql = String.format("delete from LeaveOfficeApply loa where loa.id in (%s)", ids);
        this.dataAccessor.batchUpdate(new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                return session.createQuery(deleteSql);
            }
        });
        return true;
    }

    @Override
    public boolean delete(Long id)throws Exception{
        dataAccessor.delete(get(id));
        return true;
    }

    //得到当前登录用户的离职申请
    @Override
    public LimitedList<LeaveOfficeApply> findLeaveOfficeApplyByUser(Long userId)throws Exception{
        Assert.notNull(userId, "user id is required");
        User user = dataAccessor.get(User.class, userId);
        Assert.notNull(user, "user is required");
        return dataAccessor.findByHql(LeaveOfficeApply.class, "from LeaveOfficeApply loa where loa.work.id = ? order by loa.id asc", new Object[]{user.getWork().getId()}, null, null);
    }

    //查找hr未审核或未过核的列表
    @Override
    public LimitedList<LeaveOfficeApply> findLeaveOfficeApplyForHrList(final Long organizationId, final SearchFormPojo searchFormPojo, Limit limit, Sorter sorter) throws Exception {
        Assert.notNull(organizationId, "organization id is required");
        if(searchFormPojo == null){
            return dataAccessor.findByHql(LeaveOfficeApply.class, "from LeaveOfficeApply loa where loa.organization.id = ? and loa.hrStatus <> ?", new Object[]{organizationId, AuditStatus.pass_audit}, limit, sorter);
        }
        return dataAccessor.findByQuery(LeaveOfficeApply.class, new QueryCreator(){
            @Override
            public Query createQuery(Session session) {
                DynamicQuery query = new DynamicQuery("from LeaveOfficeApply loa where 1=1");
                query.appendIfNotNull("and loa.create_date > :startDate","startDate", searchFormPojo.getStartDate());
                query.appendIfNotNull("and loa.create_date < :endDate","endDate", searchFormPojo.getEndDate());
                if(searchFormPojo.getOrgId() != null && searchFormPojo.getOrgId() > 0){
                    Organization o = dataAccessor.get(Organization.class, searchFormPojo.getOrgId());
                    if(o != null){
                        query.append("and loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                    }
                }else{
                    query.append("and loa.organization.id=:topOrg", "topOrg", organizationId);
                }
                query.append("and loa.hrStatus <> :hrStatus", "hrStatus", AuditStatus.pass_audit);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", searchFormPojo.getDutyId());
                return query.build(session);
            }
        }, limit, sorter);
    }

    //查找hr已过核且第二审核未过核或未审核的列表
    @Override
    public LimitedList<LeaveOfficeApply> findLeaveOfficeApplyForAudit(final Long organizationId, final SearchFormPojo searchFormPojo, Limit limit, Sorter sorter) throws Exception {
        Assert.notNull(organizationId, "organization id is required");
        if(searchFormPojo == null){
            return dataAccessor.findByHql(LeaveOfficeApply.class, "from LeaveOfficeApply loa where loa.organization.id = ? and loa.hrStatus = ? and loa.status <> ?", new Object[]{organizationId, AuditStatus.pass_audit, AuditStatus.pass_audit}, limit, sorter);
        }
        return dataAccessor.findByQuery(LeaveOfficeApply.class, new QueryCreator(){
            @Override
            public Query createQuery(Session session) {
                DynamicQuery query = new DynamicQuery("from LeaveOfficeApply loa where 1=1");
                query.appendIfNotNull("and loa.create_date > :startDate","startDate", searchFormPojo.getStartDate());
                query.appendIfNotNull("and loa.create_date < :endDate","endDate", searchFormPojo.getEndDate());
                if(searchFormPojo.getOrgId() != null && searchFormPojo.getOrgId() > 0){
                    Organization o = dataAccessor.get(Organization.class, searchFormPojo.getOrgId());
                    if(o != null){
                        query.append("and loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                    }
                }else{
                    query.append("and loa.organization.id=:topOrg", "topOrg", organizationId);
                }
                query.appendIfNotNull("and loa.hrStatus = :hrStatus", "hrStatus", AuditStatus.pass_audit);
                query.appendIfNotNull("and loa.status <> :status", "status", AuditStatus.pass_audit);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", searchFormPojo.getDutyId());
                return query.build(session);
            }
        }, limit, sorter);
    }

    //查找hr已过核且第二审核已过核的列表
    @Override
    public LimitedList<LeaveOfficeApply> findLeaveOfficeApplyForList(final Long organizationId, final SearchFormPojo searchFormPojo, Limit limit, Sorter sorter) throws Exception {
        Assert.notNull(organizationId, "organization id is required");
        if(searchFormPojo == null){
            return dataAccessor.findByHql(LeaveOfficeApply.class, "from LeaveOfficeApply loa where loa.organization.id = ? and loa.hrStatus = ? and loa.status = ?", new Object[]{organizationId, AuditStatus.pass_audit, AuditStatus.pass_audit}, limit, sorter);
        }
        return dataAccessor.findByQuery(LeaveOfficeApply.class, new QueryCreator(){
            @Override
            public Query createQuery(Session session) {
                DynamicQuery query = new DynamicQuery("from LeaveOfficeApply loa where 1=1");
                query.appendIfNotNull("and loa.create_date > :startDate","startDate", searchFormPojo.getStartDate());
                query.appendIfNotNull("and loa.create_date < :endDate","endDate", searchFormPojo.getEndDate());
                if(searchFormPojo.getOrgId() != null && searchFormPojo.getOrgId() > 0){
                    Organization o = dataAccessor.get(Organization.class, searchFormPojo.getOrgId());
                    if(o != null){
                        query.append("and loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                    }
                }else{
                    query.append("and loa.organization.id=:topOrg", "topOrg", organizationId);
                }
                query.append("and loa.hrStatus = :hrStatus", "hrStatus", AuditStatus.pass_audit);
                query.append("and loa.status = :status", "status", AuditStatus.pass_audit);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", searchFormPojo.getDutyId());
                return query.build(session);
            }
        }, limit, sorter);
    }

    //保存HR审核信息
    @Override
    public void saveLeaveOfficeApplyHrAuditInfo(LeaveOfficeApplyHrAuditInfo info) {
        Assert.notNull(info, "info is required");
        Assert.notNull(info.getLeaveOfficeApply(), "apply is required");
        dataAccessor.save(info);
        LeaveOfficeApply apply = info.getLeaveOfficeApply();
        apply.setHrStatus(info.getAgree() ? AuditStatus.pass_audit : AuditStatus.reject_audit);
        if(info.getAgree()){
            apply.setReason(info.getLeaveReason());
        }
        dataAccessor.save(apply);
    }


    //查询离职审核流程记录
    @Override
    public List<List<LeaveOfficeApplyAuditing>> findLeaveOfficeApplyAuditing(Long applyId) throws Exception {
        return auditingRepository.findByHql(LeaveOfficeApplyAuditing.class, "from LeaveOfficeApplyAuditing ta where ta.apply.id = ?", new Object[]{applyId});
    }

    //保存离职审核流程记录
    @Override
    public void saveLeaveOfficeApplyAuditingProcess(Long applyId) {
        saveLeaveOfficeApplyAuditing(dataAccessor.get(LeaveOfficeApply.class, applyId), 1);
    }

    //创建审核流程
    private void saveLeaveOfficeApplyAuditing(LeaveOfficeApply apply, int auditNo) {
        auditingRepository.saveAuditingProcess(apply, auditNo);
    }

    @Override
    public List<Object[]> findForReasonChart(Long searchOrgId, final Long dutyId, final String edu, final String sex, final int year, final int month)throws Exception{
        Assert.notNull(searchOrgId, "search org id is required");
        final Organization o = dataAccessor.get(Organization.class, searchOrgId);
        Assert.notNull(o, "organization is required");

        //return dataAccessor.findByHql(Object[].class, "select count(*) c, leaveReason from LeaveOfficeApply loa where loa.work.organization.treePath like '"+o.getTreePath()+"%' and loa.leaveDate >= ? and loa.leaveDate <= ? group by leaveReason  order by c desc", new Object[]{startDate, endDate}, null, null);
        return  dataAccessor.findBySafeQuery(Object[].class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                //DynamicQuery query = new DynamicQuery("select count(*) as c, reason from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%' and loa.year=:year and loa.month=:month group by reason order by 1 desc");
                DynamicQuery query = new DynamicQuery("select count(*) as c, reason from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                query.append("and loa.year = :year","year", year);
                query.append("and loa.month = :month","month", month);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", dutyId);
                query.appendIfNotEmpty("and loa.work.baseId.certificate = :edu", "edu", edu);
                query.appendIfNotEmpty("and loa.work.baseId.sex = :sex", "sex", sex);
                query.append("group by reason order by 1 desc");
                return query.build(session);
            }
        }, null, null);
    }

    @Override
    public List<Object[]> findForSexChart(Long searchOrgId, final Long dutyId, final String edu, final String reason, final int year, final int month)throws Exception{
        Assert.notNull(searchOrgId, "search org id is required");
        final Organization o = dataAccessor.get(Organization.class, searchOrgId);
        Assert.notNull(o, "organization is required");

        Calendar c = Calendar.getInstance();
        c.set(Calendar.YEAR, year);
        c.set(Calendar.MONTH, month-1);
        if(month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12){
            c.set(Calendar.DATE, 31);
        }else if(month == 2){
            c.set(Calendar.DATE, 28);
        }else{
            c.set(Calendar.DATE, 30);
        }
        c.set(Calendar.HOUR_OF_DAY, 23);
        c.set(Calendar.MINUTE, 59);
        c.set(Calendar.SECOND, 59);
        final Date endDate = c.getTime();

        c.add(Calendar.MONTH, -5);
        final Date startDate = c.getTime();

        //return dataAccessor.findByHql(Object[].class, "select count(*) c, leaveReason from LeaveOfficeApply loa where loa.work.organization.treePath like '"+o.getTreePath()+"%' and loa.leaveDate >= ? and loa.leaveDate <= ? group by leaveReason  order by c desc", new Object[]{startDate, endDate}, null, null);
        return  dataAccessor.findBySafeQuery(Object[].class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                //DynamicQuery query = new DynamicQuery("select count(*) as c, reason from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%' and loa.year=:year and loa.month=:month group by reason order by 1 desc");
                DynamicQuery query = new DynamicQuery("select count(*) as c, loa.work.baseId.sex, loa.year, loa.month from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                query.append("and loa.leaveDate > :startDate","startDate", startDate);
                query.append("and loa.leaveDate <= :endDate","endDate", endDate);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", dutyId);
                query.appendIfNotEmpty("and loa.work.baseId.certificate = :edu", "edu", edu);
                query.appendIfNotEmpty("and loa.reason = :reason", "reason", reason);
                query.append("group by loa.year, loa.month, loa.work.baseId.sex order by loa.year desc, loa.month desc");
                return query.build(session);
            }
        }, null, null);
    }

    public List<Object[]> findForNumberRank(Long searchOrgId, final Long dutyId, final String edu, final String sex, final String reason, final Date startDate, Limit limit)throws Exception{
        Assert.notNull(searchOrgId, "search org id is required");
        final Organization o = dataAccessor.get(Organization.class, searchOrgId);
        Assert.notNull(o, "organization is required");

        return  dataAccessor.findBySafeQuery(Object[].class, new QueryCreator() {
            @Override
            public Query createQuery(Session session) {
                //DynamicQuery query = new DynamicQuery("select count(*) as c, reason from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%' and loa.year=:year and loa.month=:month group by reason order by 1 desc");
                DynamicQuery query = new DynamicQuery("select count(*) as c, loa.work.organization.fullName, loa.work.organization.id from LeaveOfficeApply loa where loa.work.organization.treePath like '" + o.getTreePath() + "%'");
                query.append("and loa.leaveDate > :startDate","startDate", startDate);
                query.appendIfNotNull("and loa.work.duty.id = :dutyId", "dutyId", dutyId);
                query.appendIfNotEmpty("and loa.work.baseId.certificate = :edu", "edu", edu);
                query.appendIfNotEmpty("and loa.work.baseId.sex = :sex", "sex", sex);
                query.appendIfNotEmpty("and loa.reason = :reason", "reason", reason);
                query.append("group by loa.work.organization.id order by 1 desc");
                return query.build(session);
            }
        }, limit, null);
    }

    public List<Object[]> findForRatioRank(Long searchOrgId, Long dutyId, String edu, String sex, String reason, int year, int month)throws Exception{
        Assert.notNull(searchOrgId, "search org id is required");
        final Organization o = dataAccessor.get(Organization.class, searchOrgId);
        Assert.notNull(o, "organization is required");

        Calendar c = Calendar.getInstance();
        c.set(Calendar.YEAR, year);
        c.set(Calendar.MONTH, month-1);
        c.set(Calendar.DATE, 1);
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        List<Object[]> list = findForNumberRank(searchOrgId, dutyId, edu, sex, reason, c.getTime(), null);
        if(list != null && list.size() > 0){
            StringBuffer sb = new StringBuffer();
            for(Object[] objects : list){ //count, o_name, o_id
                sb.append((Long) objects[2]);
                sb.append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            try {
                List<Object[]> list2 = dataAccessor.findBySafeHql(Object[].class, "select count(*), w.organization.id from EmployeeinfoWork w where w.baseId.state='1'and w.organization.id in ("+sb.toString()+") group by w.organization.id", null, null, null);
                if(list2 != null && list2.size() > 0){//sum, o_id
                    Map<Long, Long> map = new HashMap<Long, Long>(list2.size());
                    for(Object[] objects : list2){
                        map.put((Long)objects[1], (Long)objects[0]);
                    }
                    List<Object[]> result = new ArrayList<Object[]>();
                    DecimalFormat df = new DecimalFormat("##.##%");
                    for(Object[] objects : list){
                        Long oid = (Long)objects[2];
                        if(map.containsKey(oid)){
                            Long leaveCount = (Long)objects[0];
                            Long workCount = map.get(oid);
                            double ratio = (double)leaveCount/(double)workCount;
                            result.add(new Object[]{oid, (String)objects[1], leaveCount, workCount, ratio, df.format(ratio)});//o_id, o_name, leave_count, o_sum, 0.1111, 11.11%
                        }
                    }
                    Collections.sort(result, new MyComparetor());
                    for(Object[] objects : result){
                        System.out.println("wxn--2--"+objects[0]+":"+objects[1]+":"+objects[2]+":"+objects[3]+":"+objects[4]+":"+objects[5]);
                    }
                    return result;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public void leave(LeaveOfficeApply apply) throws Exception {
        Assert.notNull(apply, "apply is required");
        Assert.notNull(apply.getId(), "apply id is required");
        apply = get(apply.getId());
        Assert.notNull(apply, "search apply is required");
        apply.setLeaveOffice(true);
        dataAccessor.save(apply);
        EmployeeinfoBase employee = dataAccessor.get(EmployeeinfoBase.class, apply.getWork().getBaseId().getId());
        employee.setState("2");
        dataAccessor.save(employee);
    }

    private class MyComparetor implements Comparator<Object[]>{
        public int compare(Object[] o1, Object[] o2) {
            if(o1 == null || o2 == null || o1.length != o2.length || o1.length !=6){
                return 0;
            }
            double r1 = (Double)o1[4];
            double r2 = (Double)o2[4];
            if (r1 > r2) {
                return 1; // 第一个大于第二个
            } else if (r1 < r2) {
                return -1;// 第一个小于第二个
            } else {
                return 0; // 等于
            }
        }
    }
}
